package com.css.fxfzypg.modules.similitudeAnalyse.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.modules.auxiliaryDecisionManage.auxiliaryDecision.vo.AreaResultVO;
import com.css.fxfzypg.modules.baseData.dto.PetIndProjectDto;
import com.css.fxfzypg.modules.similitudeAnalyse.entity.*;
import com.css.fxfzypg.modules.similitudeAnalyse.param.QueParams;
import com.css.fxfzypg.modules.similitudeAnalyse.param.QueParams1;
import com.css.fxfzypg.modules.similitudeAnalyse.repository.SimAnalyseRepository;
import com.css.fxfzypg.modules.similitudeAnalyse.vo.SimAnaResultVo;
import com.css.fxfzypg.modules.suggestinfo.manage.disasterscenarioManage.repository.entity.DisasterScenarioManageEntity;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class SimAnalyseRepositoryImpl implements SimAnalyseRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    SysAreaService sysAreaService;

    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;


    @Override
    public int save(SimAnaResult simAnaResult) {
        String sql = "insert into ypg_similitude_analyse_result(id,real_time,real_earthquake_level,real_longitude,real_latitude,real_depth," +
                "real_location, his_pr_id, distance_target, distance, seismic_capacity_target,real_house_total, house_total, real_dearth_house_total, " +
                "dearth_house_total, earthquake_level_target, similarity_calculation_result, province_name, cata_id) " +
                "values(:id, :realTime, :realEarthquakeLevel, :realLongitude, :realLatitude, :realDepth, :realLocation, :hisPrId, :distanceTarget, " +
                ":distance, :seismicCapacityTarget, :realHouseTotal, :houseTotal, :realDearthHouseTotal, :dearthHouseTotal, :earthquakeLevelTarget, " +
                ":similarityCalculationResult, :provinceName, :cataId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(simAnaResult);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public Map<String, Object> getPage(QueParams1 queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select a.*,b.dhp_task_name dhpTaskName,b.dhp_epicenter_longitude dhpEpicenterLongitude," +
                "b.dhp_epicenter_latitude dhpEpicenterLatitude,b.dhp_location dhpLocation,b.dhp_magnitude dhpMagnitude," +
                "b.dhp_year dhpYear from ypg_similitude_analyse_result a left join data_his_pr b on a.his_pr_id=b.id ");
        StringBuilder whereSql = new StringBuilder(" where del_flag='0'");
        StringBuilder orderSql = new StringBuilder(" order by real_time desc,similarity_calculation_result desc,distance desc,b.dhp_year desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getRealEarthquakeLevel())) {
            whereSql.append(" and CAST( b.dhp_magnitude as VARCHAR) like '%" + queParams.getRealEarthquakeLevel() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getLongitude())) {
            whereSql.append(" and CAST( a.real_longitude as VARCHAR) like '%" + queParams.getLongitude() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getLatitude())) {
            whereSql.append(" and CAST( a.real_latitude as VARCHAR) like '%" + queParams.getLatitude() + "%'");
        }

        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.YPG_PRI_YW_ROLE) || roles.contains(FxfzConstants.YPG_PRI_GL_ROLE)) {
            String areaNameByAreaId = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            if (!PlatformObjectUtils.isEmpty(areaNameByAreaId)) {
                whereSql.append(" and a.province_name like '%" + areaNameByAreaId + "%' and b.dhp_location like '%" + areaNameByAreaId + "%'");
            }
        }

        // //时间范围
        // if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & !PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
        //     whereSql.append("and real_time between '" + queParams.getStartTime() + "' and '" + queParams.getEndTime() + "'");
        // }
        // //大于开始时间
        // if (!PlatformObjectUtils.isEmpty(queParams.getStartTime()) & PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
        //     whereSql.append("and real_time  >='" + queParams.getStartTime() + "'");
        // }
        // //小于结束时间
        // if (!PlatformObjectUtils.isEmpty(queParams.getEndTime()) & PlatformObjectUtils.isEmpty(queParams.getStartTime())) {
        //     whereSql.append("and real_time  <='" + queParams.getEndTime() + "'");
        // }


        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SimAnaResultVo.class)).size();
        List<SimAnaResultVo> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SimAnaResultVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public SimAnaResult fingById(String id) {
        String sql = "select * from ypg_similitude_analyse_result where id = ? ";
        SimAnaResult simAnaResult = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SimAnaResult.class), id);
        return simAnaResult;
    }

    @Override
    public void deleteAllData() {
        String sql = "delete from ypg_similitude_analyse_result";
        jdbcTemplate.update(sql);
    }

    @Override
    public void saveRealEarthquake(RealEarthquakeEntity entity) {
        String sql = "insert into real_earthquake_info(cata_id,o_time,m,epi_lat,epi_lon,epi_depth,auto_flag, " +
                "location_c,geom) " +
                "values(:cataId,:oTime,:m,:epiLat,:epiLon,:epiDepth,:autoFlag, :locationC,ST_GeometryFromText(:geom,4490))";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void saveRealSeismicInfluenceflied(RealSeismicInfluenceflyPbe entity) {
        String sql = "insert into real_seismic_influenceflied_pbe(uuid,earthquake_level,dz_intensity,long_x,short_y," +
                "geom,data_source, remark, cata_id) " +
                "values(:uuid,:earthquakeLevel,:dzIntensity,:longX,:shortY,ST_GeometryFromText(:geom,4490),:dataSource, :remark, :cataId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void saveinfluenceflyList(List<RealSeismicInfluenceflied> influenceflyList){
        try {
            String sql = "insert into real_seismic_influenceflied(uuid,longitude,latitude,earthquake_level,fault_angle," +
                    "dz_intensity,long_x,short_y,geom,data_source, remark, cata_id) " +
                    "values(:uuid,:longitude,:latitude,:earthquakeLevel,:faultAngle,:dzIntensity,:longX,:shortY," +
                    "ST_GeometryFromText(:geom,4490),:dataSource, :remark, :cataId)";
            namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(influenceflyList));
        } catch (Exception e) {
            throw new RuntimeException("批量插入失败", e);
        }
    }

    @Override
    public void delRealEarthquake(String cataId) {
        jdbcTemplate.update("delete from real_earthquake_info where cata_id='"+cataId+"'");
    }

    @Override
    public RealEarthquakeEntity getById(String cataId) {
        String sql = "select * from real_earthquake_info where cata_id = ? ";
        List<RealEarthquakeEntity> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RealEarthquakeEntity.class), cataId);
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public List<RealEarthquakeEntity> getByall() {
        String sql = "select * from real_earthquake_info";
        List<RealEarthquakeEntity> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RealEarthquakeEntity.class));
        return query;
    }

    @Override
    public String getNewestSimAnaResultId() {
        StringBuilder sql = new StringBuilder("select * from ypg_similitude_analyse_result a ");
        sql.append(" where 1=1").append(" order by real_time desc,similarity_calculation_result desc,distance desc limit 1");
        //SimAnaResult simAnaResult = jdbcTemplate.queryForObject(sql.toString(), SimAnaResult.class);
        List<SimAnaResult> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SimAnaResult.class));
        if (query != null && query.size()>0){
            return query.get(0).getId();
        }else {
            return null;
        }
    }

    @Override
    public int saveRealEqDisaster(RealEqDisaster entity) {
        String sql = "insert into real_earthquake_disaster(id, jeds_id, event_abbreviation,jeds_date,jeds_time, jeds_longitude, jeds_latitude, magnitude, " +
                "reference_position, depth, maximum_intensity, dead_number, cause_of_death, injuries, num_resettled, rescue_force_input, " +
                "medical_force_input, loss_volume, infrastructure_damage, geological_hazard_situation, secondary_fire, secondary_flood, " +
                "secondary_explosion, hazardous_chemicals_leaked, des_earthquake_damage, note, jeds_zhzp, isoline, isoline_pic, intensity_map, " +
                "attachment, location, location1,real_province,province)" +
                " values(:redId, :jedsId, :eventAbbreviation, :jedsDate, :jedsTime, :jedsLongitude, :jedsLatitude, :magnitude, :referencePosition, :depth, :maximumIntensity, " +
                ":deadNumber, :causeOfDeath, :injuries, :numResettled, :rescueForceInput, :medicalForceInput, :lossVolume, :infrastructureDamage, " +
                ":geologicalHazardSituation, :secondaryFire, :secondaryFlood, :secondaryExplosion, :hazardousChemicalsLeaked, :desEarthquakeDamage, " +
                ":note, :jedsZhzp, :isoline, :isolinePic, :intensityMap, :attachment, :location, :location1, :realProvince, :province) ";

        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public void delRealSeismicInfluencefliedPbe(String cataId) {
        String sql = "delete from real_seismic_influenceflied_pbe where cata_id='"+cataId+"'";
        jdbcTemplate.update(sql);
    }
    @Override
    public void delRealSeismicInfluenceflied(String cataId) {
        String sql = "delete from real_seismic_influenceflied where cata_id='"+cataId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<RealSeismicInfluenceflied> getRealSeismicInfluenceflied(String cataId, String dzIntensity) {
        StringBuilder sql = new StringBuilder("select * from real_seismic_influenceflied where cata_id='");
        sql.append(cataId).append("'");
        if(!"".equals(dzIntensity)){
            sql.append(" and dz_intensity='").append(dzIntensity).append("'");
        }
        return jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<>(RealSeismicInfluenceflied.class) );
    }

    @Override
    public List<AreaResultVO> getAreaMessage(String cataId, String codePrefix, List<String> dzIntensityList) {
        StringBuilder sql = new StringBuilder(" SELECT distinct(T.name),A.dz_intensity from  district_boundary_county T,real_seismic_influenceflied A  WHERE 1=1 and  A.dz_intensity in ('"+String.join("','", dzIntensityList)+"')");
        sql.append(" and A.cata_id='").append(cataId).append("' and T.code LIKE'").append(codePrefix).append("%' and ST_Intersects (T.geom,A.geom)").append(" order by A.dz_intensity desc");
        return jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<>(AreaResultVO.class) );
    }
}
